/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package assignment_cp3120.DAO.mysqlDAO;

import assignment_cp3120.DAO.UserDAO;
import assignment_cp3120.dataObjects.User;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * A class to hold all of the user querys
 * @author Richard Irons
 */
public class MysqlUserDAO implements UserDAO{
    /**
     * A function that is the query to get all the data for a user and return that user.
     * Will return a new user if something went wrong
     * @param username
     * Username to search for in the DB
     * @return
     * The found user else null.
     */
    public User getUser(String username) {
        User user = new User();
        Connection c = MysqlDA0Factory.createConnection();
        try{            
            Statement statment = c.createStatement();
            ResultSet rs = statment.executeQuery(
                    "SELECT * FROM USER WHERE USERNAME = \""
                    +username
                    +"\"");
            if(rs.isBeforeFirst()){
                rs.next();
            }            
            if(rs.first()){
                user.setUsername(rs.getString("USERNAME"));
                user.setPassword(rs.getString("PASSWORD"));
                user.setFirstname(rs.getString("F_NAME"));
                user.setLastname(rs.getString("L_NAME"));
                user.setDOB(rs.getDate("DOB").toString());
                user.setAddress(rs.getString("ADDRESS"));
                user.setSuburb(rs.getString("SUBURB"));
                user.setState(rs.getString("STATE"));
                user.setPostcode(rs.getString("POSTCODE"));
                user.setAccountType(rs.getString("ACCOUNT_TYPE"));                
            }            
            c.close();
        }catch(SQLException e){user = null;}
        catch(NullPointerException e){
            user = null;
        }
        return user;
    }
    /**
     * Delete the given username from the DB
     * @param username
     * Username to delete
     * @return
     * True if the user is deleted
     */
    public int deleteUser(String username) {
        Connection c = MysqlDA0Factory.createConnection();
        int result = 0;
        try{
            Statement statment = c.createStatement();
            result = statment.executeUpdate(
                    "DELETE FROM `USER` WHERE USERNAME = \""
                    +username
                    +"\" LIMIT 1 ");
        
        }catch(SQLException e){
            System.out.println(e.getMessage());
        }
        return result;
    }
    /**
     * 
     * @param user
     * @return
     */
    public int addUser(User user) {
        Connection c = MysqlDA0Factory.createConnection();
        int result = 0;
        try{            
            Statement statment = c.createStatement();
            result = statment.executeUpdate(
                    "INSERT INTO 3120S109g09.USER ( "
                    + "USERNAME , "
                    + "PASSWORD , "
                    + "F_NAME , "
                    + "L_NAME , "
                    + "DOB , "
                    + "ADDRESS , "
                    + "SUBURB , "
                    + "STATE , "
                    + "POSTCODE , "
                    + "ACCOUNT_TYPE "
                    + ") VALUES ( "
                    + "\"" + user.getUsername() + "\" , "
                    + "\"" + user.getPassword() + "\" , "
                    + "\"" + user.getFirstname() + "\" , "
                    + "\"" + user.getLastname() + "\" , "
                    + "\"" + user.getDOB() + "\" , "
                    + "\"" + user.getAddress() + "\" , "
                    + "\"" + user.getSuburb() + "\" , "
                    + "\"" + user.getState() + "\" , "
                    + "\"" + user.getPostcode() + "\" , "
                    + "\"" + user.getAccountType() + "\" "
                    + ")");
        
        }catch(SQLException e){
            System.out.println(e.getMessage());
        }
        return result;
    }
}
